BigQueryのスロットの使われ方をGoogle Colabでちょっと確認する

BigQueryのスロットの使われ方をGoogle Colabでちょっと確認する

BigQueryのINFORMATION_SCHEMA.JOBS_TIMELINE_BY_*ビューからジョブの情報を取得し、 Google Colaboratory（Colab）でスロットの使われ方を可視化する。
#pandas
#可視化
#Google BigQuery
#Google Cloud (GCP)
ikeda

ikeda

facebook logohatena logotwitter logo
Clock Icon2021.04.13

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部、池田です。
BigQueryを使う上で気になるのは料金ですが、 その大きな要素となる スロット の使われ方をPandasで簡単に可視化してみました。
今回は実行環境として Google Colaboratory （以下Colab）を使いました。

余談（他の方法について）

現在プレビューの機能ですが、予約スロットを使っていると、以下でより簡単にグラフとして確認ができるようです。
【 リアルタイム モニタリングに BigQuery 管理リソースグラフを使用する 】

また、以下のサービスでもBigQueryに接続してノートブックを使うことが可能なので、 同じようなことができそうです。
【 AI Platform Notebooks を使って、さくっとノートブック環境を立ち上げて BigQuery にアクセスしてみた 】

今回はGoogle ドライブに書き出したファイルをColabで読み込んでいますが、 Colabから直接BigQueryに接続することで実現もできそうです。
【 BigQuery Storage API を使用して BigQuery データを pandas にダウンロードする 】
【 ColaboratoryでBigQueryにアクセスする3つの方法 】

今回、Google ドライブ上のファイルとColabの組み合わせにしている理由は…

  • BigQuery内の情報をファイルにする必要があって、ちょっと確認程度に可視化したいイメージ
  • コンソールからクエリ結果を保存する時には、Google ドライブが便利そう
    • ローカルやクリップボード：最大で16000行
    • Google ドライブ：最大で１GB
  • 予約スロットでない前提
  • （ファイル保存までしてしまえば）料金を気にしなくてよい

という辺りです。

BigQuery側作業

テストクエリの実行

前回 ブログ にした ラベル を付けておきたいので、 bqコマンド の query から検証用のクエリを実行しておきます。

↓こんな感じ。

クリックでコマンドを表示する/折りたたむ 
nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--label "title:devio" \
--label "case:interactive1" \
'SELECT
     a.*, b.*, c.*, d.*
 FROM
     `bigquery-public-data.samples.gsod` a,
     `bigquery-public-data.samples.gsod` b,
     `bigquery-public-data.samples.gsod` c,
     `bigquery-public-data.samples.gsod` d
 WHERE
     a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
 AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
 AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/i1.log 2>&1 &

nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--batch \
--label "title:devio" \
--label "case:batch1" \
'SELECT
     a.*, b.*, c.*, d.*
 FROM
     `bigquery-public-data.samples.gsod` a,
     `bigquery-public-data.samples.gsod` b,
     `bigquery-public-data.samples.gsod` c,
     `bigquery-public-data.samples.gsod` d
 WHERE
     a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
 AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
 AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/b1.log 2>&1 &

nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--label "title:devio" \
--label "case:interactive2" \
'SELECT
     a.*, b.*, c.*, d.*
 FROM
     `bigquery-public-data.samples.gsod` a,
     `bigquery-public-data.samples.gsod` b,
     `bigquery-public-data.samples.gsod` c,
     `bigquery-public-data.samples.gsod` d
 WHERE
     a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
 AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
 AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/i2.log 2>&1 &

nohup \
bq --location=US query \
--use_legacy_sql=false \
--use_cache=false \
--batch \
--label "title:devio" \
--label "case:batch2" \
'SELECT
     a.*, b.*, c.*, d.*
 FROM
     `bigquery-public-data.samples.gsod` a,
     `bigquery-public-data.samples.gsod` b,
     `bigquery-public-data.samples.gsod` c,
     `bigquery-public-data.samples.gsod` d
 WHERE
     a.station_number = b.station_number AND a.wban_number = b.wban_number AND a.year = b.year AND a.month = b.month AND a.day = b.day
 AND a.station_number = c.station_number AND a.wban_number = c.wban_number AND a.year = c.year AND a.month = c.month AND a.day = c.day
 AND a.station_number = d.station_number AND a.wban_number = d.wban_number AND a.year = d.year AND a.month = d.month AND a.day = d.day;'>> ~/devio/b2.log 2>&1 &

BigQueryの一般公開データセット の気象情報の gsod サンプルテーブルを適当に結合して（もっと実務的なクエリが良かったのですが…）、 キャッシュ無しで通常1分弱ほどかかるように調整しました。 そのクエリを インタラクティブ（オンデマンド）クエリとバッチクエリ として、それぞれ2本ずつ nohup でほぼ同時に実行しています。

テストクエリの結果（ジョブ情報）

↓のクエリで INFORMATION_SCHEMA から ジョブの情報 （ JOBS_BY_PROJECT ビュー）を参考までに簡単に確認します。

SELECT
    labels[SAFE_OFFSET(0)].value AS title, labels[SAFE_OFFSET(1)].value AS `case`,
    ROUND(TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) / 1000, 1) AS time_sec,
    ROUND(total_slot_ms / TIMESTAMP_DIFF(end_time, start_time, MILLISECOND)) AS avg_slots,
    total_slot_ms, total_bytes_billed, cache_hit,
    (error_result IS NOT NULL) AS has_error,
    creation_time, job_id, priority, start_time, end_time,
    state, total_bytes_processed,
    REGEXP_REPLACE(LEFT(query, 100), "\r?\n", " ") AS query
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
    creation_time >= "2021-04-09 09:00:00" -- UTC, partition
AND labels[SAFE_OFFSET(0)].value = "devio"
ORDER BY creation_time DESC;

↓結果

+-------+--------------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+------------------------------------------------------------------------------------------------------+
| title |     case     | time_sec | avg_slots | total_slot_ms | total_bytes_billed | cache_hit | has_error |    creation_time    |                   job_id                   |  priority   |     start_time      |      end_time       | state | total_bytes_processed |                                                query                                                 |
+-------+--------------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+------------------------------------------------------------------------------------------------------+
| devio | batch1       |     56.2 |     162.0 |       9111267 |        17291018240 |     false |     false | 2021-04-09 09:34:35 | foo                                        | BATCH       | 2021-04-09 09:36:11 | 2021-04-09 09:37:07 | DONE  |           17290009238 | SELECT      a.*, b.*, c.*, d.*  FROM      `bigquery-public-data.samples.gsod` a,      `bigquery-publ |
| devio | interactive2 |     52.0 |     167.0 |       8675341 |        17291018240 |     false |     false | 2021-04-09 09:34:35 | bar                                        | INTERACTIVE | 2021-04-09 09:34:35 | 2021-04-09 09:35:27 | DONE  |           17290009238 | SELECT      a.*, b.*, c.*, d.*  FROM      `bigquery-public-data.samples.gsod` a,      `bigquery-publ |
| devio | batch2       |     51.1 |     183.0 |       9363212 |        17291018240 |     false |     false | 2021-04-09 09:34:35 | hoge                                       | BATCH       | 2021-04-09 09:35:36 | 2021-04-09 09:36:27 | DONE  |           17290009238 | SELECT      a.*, b.*, c.*, d.*  FROM      `bigquery-public-data.samples.gsod` a,      `bigquery-publ |
| devio | interactive1 |     57.7 |     152.0 |       8778882 |        17291018240 |     false |     false | 2021-04-09 09:34:35 | fuga                                       | INTERACTIVE | 2021-04-09 09:34:35 | 2021-04-09 09:35:33 | DONE  |           17290009238 | SELECT      a.*, b.*, c.*, d.*  FROM      `bigquery-public-data.samples.gsod` a,      `bigquery-publ |
+-------+--------------+----------+-----------+---------------+--------------------+-----------+-----------+---------------------+--------------------------------------------+-------------+---------------------+---------------------+-------+-----------------------+------------------------------------------------------------------------------------------------------+

所要時間 time_sec は4本とも1分弱で、 ジョブの作成時間 creation_time が同じなのに、 start_time や end_time にばらつきがありますね。

ジョブのタイムラインの取得と加工

前節では、ジョブごとの情報でざっくり見ましたが、 各ジョブの1秒ごとの挙動を JOBS_TIMELINE_BY_PROJECT ビューから取得して、 可視化しやすいように加工します。（加工は別にPnadas側でやっても良いです。）
【 INFORMATION_SCHEMA を使用したタイムスライスごとのジョブのメタデータの取得 】

↓こんな感じで取得＆加工。

SELECT *,
       ROUND(SUM(period_slots) OVER (PARTITION BY job_id) / time_sec) AS avg_slots  -- 平均使用スロット
FROM (SELECT job.title, job.case, tl.job_id,
             TIMESTAMP_DIFF(tl.period_start, MIN(tl.period_start) OVER (PARTITION BY job.title), SECOND) AS elapsed_sec,  -- 初めのジョブが開始してからの経過時間
             tl.period_slot_ms / 1000 AS period_slots,  -- この期間の使用スロット
             tl.state,
             TIMESTAMP_DIFF(MAX(tl.job_end_time) OVER (PARTITION BY tl.job_id),
                            MIN(tl.job_start_time) OVER (PARTITION BY tl.job_id), MILLISECOND) / 1000 AS time_sec,  -- 合計処理時間
             ROUND(MAX(tl.period_slot_ms) OVER (PARTITION BY tl.job_id) / 1000) AS max_slots  -- 最大使用スロット
      FROM `region-us`.INFORMATION_SCHEMA.JOBS_TIMELINE_BY_PROJECT tl,
           (SELECT DISTINCT labels[SAFE_OFFSET(0)].value AS title, labels[SAFE_OFFSET(1)].value AS `case`, job_id
            FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
            WHERE labels[SAFE_OFFSET(0)].value = 'devio') job
      WHERE tl.job_id = job.job_id)
ORDER BY title, `case`, elapsed_sec;

時刻だと分かりにくいので、経過時間として加工しています。 JOBS_TIMELINE_BY_PROJECT ビューはラベル情報を持たないので、 前述の JOBS_BY_PROJECT ビューと結合しています。 （ラベルを使っていない場合は、結合せずに job_id をキーとして見ていけば良いはずです。）

↓結果（冒頭一部）

+-------+--------+--------------------------------------------+-------------+--------------+---------+----------+-----------+-----------+
| title |  case  |                   job_id                   | elapsed_sec | period_slots |  state  | time_sec | max_slots | avg_slots |
+-------+--------+--------------------------------------------+-------------+--------------+---------+----------+-----------+-----------+
| devio | batch1 | foo                                        |           0 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           1 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           2 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           3 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           4 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           5 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           6 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           7 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           8 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
| devio | batch1 | foo                                        |           9 |          0.0 | PENDING |   56.211 |     935.0 |     162.0 |
…

経過時間 elapsed_sec 時点での、スロット使用数 period_slots が取得できそうですね。 （batch1は処理開始が遅いので、冒頭はスロットが0で続く。）

このクエリの結果をGoogle ドライブに保存します。

マイドライブ直下にフォルダと共に出力されました。

この結果をColabで参照していきます。

Colab側作業

実装

コードの記入エリアを追加しながら書いていきます。

ノートブックに Google ドライブをマウント 。

from google.colab import drive

drive.mount('/content/drive')

（出力されたURLから取得したコードを入力してやる必要があります。）

前章で出力したファイルを読み込む。

import pandas as pd

file_dir = "{Google ドライブ上のパス}"
file_path = file_dir + "{Google ドライブ上のファイル名}.csv"
df = pd.read_csv(file_path,
                 header=0, sep=",", encoding="utf-8")
df.info()


case 列ごとに折れ線グラフとして、 スロット使用数 period_slots と平均のスロット使用数 avg_slots を出力。

from matplotlib import pyplot as plt

cases = df["case"].unique()
print("cases:", cases)

plt.style.use("ggplot")
for c in cases:
    plt.figure()
    df[df["case"] == c].plot(kind="line", title=c,
                             x="elapsed_sec",
                             y=["period_slots", "avg_slots"])
    plt.savefig(file_dir + "{}_line.png".format(c))

plt.close('all')


ピボットして、積み上げの折れ線グラフ（面グラフ）として出力。

pivot = df.pivot(index="elapsed_sec", columns="case")["period_slots"]
pivot.info()

titles = df["title"].unique()

plt.figure()
pivot.plot.area(title=titles[0], linewidth=0)
plt.savefig(file_dir + "{}_lines.png".format(titles[0]))
plt.close("all")


Google ドライブのマウントを解除。

drive.flush_and_unmount()


↑こんな感じで実装すると、各グラフがGoogle ドライブに出力されます。（結果画像は次章。）

↓ちなみに、plt.close("all") しなければ、Colab上で表示もできます。

結果

前章までの作業で可視化してみた結果がこんな感じです。


内容が同じクエリなので、どれも同じような波形（スロットの使われ方）ですね。


積み上げてみると、今回の検証では、 「インタラクティブクエリが並列で動いた」あとに「バッチクエリが逐次で動いた」 というような挙動であったことが分かりますね。 （今回はサンプルやバリエーションが少ないのと、考察は本題ではないのでこれくらい。）

おわりに

予約スロットで フェア スケジューリング の動きなどを可視化したら楽しそうですね。
やっぱりColab便利です。

関連情報/参考にさせていただいたページ

Share this article

facebook logohatena logotwitter logo

関連記事

Google Colab Enterprise を操作してみた。基本操作と料金プランをわかりやすく解説。

Google Colab Enterprise を操作してみた。基本操作と料金プランをわかりやすく解説。

User avatar
室井靖成
2024.06.17
【ダイエット × データ分析】ダイエットデータから2ヶ月後の体重を元トレーナーが 分析/予測 してみた

【ダイエット × データ分析】ダイエットデータから2ヶ月後の体重を元トレーナーが 分析/予測 してみた

User avatar
室井靖成
2024.06.05
Data Wrangler Extension for Visual Studio Code がGAになったよ

Data Wrangler Extension for Visual Studio Code がGAになったよ

User avatar
大谷 一輝
2024.05.01
[初心者向け]pandasでCloud StorageのCSVファイルを読み込んでみた

[初心者向け]pandasでCloud StorageのCSVファイルを読み込んでみた

User avatar
根本夏瑠
2024.04.26
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.